Daily Monitoring TSQL Queries

The basic monitoring points to keep in mind while monitoring a server:

• Database state
• Log space
• Jobs
• Drive space
• If Backup is done
• Blocking
• Long Running
• Logshipping is in sync

---

CHECKING WINDOWS INFO

WINVER

Checking Server Services MSSQLSERVER, AGENT 

SELECT * FROM SYS.DM_SERVER_SERVICES

CHECKING DATABASE STATUS WHERE NOT ONLINE

SELECT NAME,STATE_DESC,* FROM SYS.DATABASES WHERE STATE_DESC<>'ONLINE'

CHECKING WHEN DATABASE STARTED

SELECT NAME,STATE_DESC,* FROM SYS.DATABASES WHERE NAME='TEMPDB'

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

---

Database States

1. ONLINE: Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
2. OFFLINE: Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
3. RESTORING: One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
4. RECOVERING : Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
5. RECOVERY PENDING: SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
6. SUSPECT: At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
7. EMERGENCY: User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

Checking Database State:

select name,state_desc,* from sys.databases where state_desc<>'online'

---

Blocking and Long Running queries:
Blocking can created slowness on the server so it is very important to check for any blocking  in the sessions on the server

Also long running queries can take up high CPU and cause slowness on the server

Monitoring Sessions, blocking:

SELECT SP.SPID, DB_NAME(SP.DBID) AS DBNAME,LASTWAITTYPE,SP.BLOCKED,CMD,TEXT,P.REQUIRED_MEMORY_KB,P.GRANTED_MEMORY_KB,P.USED_MEMORY_KB,P.QUERY_COST

,SP.CPU,SP.PHYSICAL_IO,* FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SH

LEFT JOIN SYS.DM_EXEC_QUERY_MEMORY_GRANTS P

ON SP.SPID=P.SESSION_ID

WHERE STATUS<>'SLEEPING' AND SP.SPID<>@@SPID

ORDER BY SP.LOGIN_TIME ASC

Checking Active Processes, status ,blocking if any:

Exec SP_WHO2 ACTIVE 

---

Check Hard Disk Space

Check for drive space on the server.

If a particular drive has low space check if any space can be reclaimed by shrinking logs or deleting unwanted backups.

Exec XP_FIXEDDRIVES

---

Log Space

• All monitor log space to see if there is any log growth 
• Shrink the logs if necessary take a log backup and shrink logs .
• Check log usage using the command dbcc sqlperf(logspace)

CHECK THE SIZE OF LOG FILE GREATER THAN 900 MB

SELECT B.NAME,(A.SIZE*8)/(1024*1024)AS LOG FROM SYS.SYSALTFILES A,SYS.SYSDATABASES B WHERE A.DBID = B.DBID AND

A.FILENAME LIKE '%.LDF'AND (A.SIZE*8)/(1024*1024)>0.9

To calculate database size(mdf+ldf)

-- CALCULATING DATABASE SIZE IN KB

SELECT name, SIZE*8 AS LOG FROM SYS.SYSALTFILES


-- CALCULATING DATABASE SIZE IN MB

SELECT name, SIZE*8/1024 AS LOG FROM SYS.SYSALTFILES



-- CALCULATING DATABASE SIZE IN GB

SELECT name, SIZE*8/(1024*1024) AS LOG FROM SYS.SYSALTFILES

SHRINKING LOG FILE IF GREATER THAN 1GB (IF STANDALONE DATABASE)

USE DATABASE

DBCC SHRINKFILE (2)

NOTE--IF LOG CANNOT SHRINKED CHANGE RECOVERY MODEL TO SIMPLE AND SHRINK, THEN CHANGE RECOVERY MODEL TO FULL (LSN START FROM 1)

NOTE--DONOT CHNAGE RECOVERY MODEL OF LOG SHIPPING DATABASE

---

Jobs:

Check for failed jobs in Job Activity Monitor

Check for backup Jobs and subplans

TO CHECK FAILED JOBS--change run date

SELECT DISTINCT B.NAME,A.RUN_DATE FROM MSDB..SYSJOBHISTORY A ,MSDB..SYSJOBS B WHERE A.JOB_ID=B.JOB_ID AND RUN_STATUS=0

AND A.RUN_DATE>=20180707

---

LOG SHIPPING DATABASE MONITORING:

If the server has logshipping configured check if the log shipping is in sync or not by using the Transaction logshipping reports.
If not in sync check if the restore or copy job is in running , else run the jobs.

If the above does not log take the log backup after the last restored log backup and  restore those logs after such restoration run the job and check.

CHECK PRIMARY SERVER JOB

USE MASTER

EXEC sp_help_log_shipping_monitor

SELECT @@SERVERNAME

EXEC sp_help_log_shipping_monitor_primary @primary_server='CLOVERCONNECT-S', @primary_database = 'TESTDB' 

---

CHECK PROGRESS OF BACKUP OR RESTORE

 
SELECT PERCENT_COMPLETE , * FROM SYS.DM_EXEC_REQUESTS WHERE PERCENT_COMPLETE<>0

---

How to resolve a slowness issue ?

Question that should 1st come our mind when there is slowness on a server are:

• Is there any blocking on the server 
• Is the max memory set enough for the server
• Check the wait types that occurred the most in the time of slowness
• Check if the Paging File on the server is set properly.
• Sleeping sessions 
• Missing indexes , Bad Indexes, Unused indexes
• Fragmentation

---